package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.OverseaConsumptionDTO;
import com.sunda.spmsoversea.dto.OverseaReport;
import com.sunda.spmsoversea.dto.WelcomePageSearchDTO;
import com.sunda.spmsoversea.dto.WelcomePageWerksOrderDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface OverseaReportMapper {

    /** 获取指定工厂仓库的物料库存，不带储位按料号求和 */
    @Select("<script>" +
            "SELECT WS.WERKS AS \"werks\",\n" +
            "       WS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "       SUM(WSI.QUANTITY) AS \"quantity\",\n" +
            "       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       MS.BASIC_UNIT AS \"basicUnit\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID " +
            "       LEFT JOIN MATERIAL_SAP MS ON WSI.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "WHERE WSI.MATERIAL_NO IS NOT NULL AND WSI.QUANTITY > 0 \n" +
            " <when test='werks!=null and werks != \"\"'> AND WS.WERKS = #{werks}</when>" +
            " <when test='whsLocationCode!=null and whsLocationCode != \"\"'> AND WS.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " <when test='materialNo!=null and materialNo != \"\"'> AND WSI.MATERIAL_NO = #{materialNo}</when>" +
            "GROUP BY WS.WERKS, WS.WHS_LOCATION_CODE, WSI.MATERIAL_NO, MS.MATERIAL_ZH_DESC, MS.MATERIAL_EN_DESC, MS.BASIC_UNIT\n" +
            "ORDER BY WS.WERKS, WS.WHS_LOCATION_CODE, WSI.MATERIAL_NO" +
            "</script>")
    List<Map<String, Object>> getWhsInventory(@Param("werks") String werks,
                                              @Param("whsLocationCode") String whsLocationCode,
                                              @Param("materialNo") String materialNo);

    /** 获取指定工厂仓库下，指定料号每天的操作日志 */
    @Select("<script>" +
            "SELECT " +
            "       RE.MATERIAL_NO AS \"materialNo\",\n" +
            "       SUM(QUANTITY) AS \"quantity\",\n" +
            "       RE.CREATE_DATE AS \"createDate\",\n" +
            "       TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE,'yyyy-MM-dd'),'yyyy-mm-dd') - TO_DATE(RE.CREATE_DATE,'yyyy-mm-dd')) AS \"days\",\n" +
            "       WERKS AS \"werks\",\n" +
            "       WHS_LOCATION_CODE AS \"whsLocationCode\"\n" +
            "FROM (\n" +
            "    SELECT WOL.MATERIAL_NO, WOL.QUANTITY,\n" +
            "        SUBSTR(TO_CHAR(WOL.CREATE_TIME, 'yyyy-MM-dd'), 0, 10) AS \"CREATE_DATE\",\n" +
            "        WOL.WERKS, WOL.WHS_LOCATION_CODE\n" +
            "    FROM WHS_OPERATE_LOG WOL\n" +
            "    WHERE WOL.WERKS = #{werks}\n" +
            " <when test='whsLocationCode!=null and whsLocationCode != \"\"'>AND WOL.WHS_LOCATION_CODE = #{whsLocationCode}</when>\n" +
            " <when test='materialNo!=null and materialNo != \"\"'>AND WOL.MATERIAL_NO = #{materialNo}</when>\n" +
            "        AND WOL.DOCUMENT_TYPE IN ('2', '4')\n" +
            "        AND WOL.OPERATION_TYPE IN ('2', '7', '12', '18', '19')\n" +
            "    ) RE\n" +
            "GROUP BY RE.MATERIAL_NO, \"CREATE_DATE\", WERKS, WHS_LOCATION_CODE\n" +
            "ORDER BY TO_DATE(\"CREATE_DATE\", 'yyyy-MM-dd') DESC" +
            "</script>")
    /** 库龄分析(按物料) */
    List<Map<String, Object>> getWhsOperate(@Param("werks") String werks,
                                            @Param("whsLocationCode") String whsLocationCode,
                                            @Param("materialNo") String materialNo);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.*,\n" +
            "       TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE,'yyyy-MM-dd'),'yyyy-mm-dd') -  TO_DATE(RE.\"createDate\", 'yyyy-mm-dd')) AS \"days\"\n" +
            "FROM (\n" +
            "    SELECT WS.WERKS AS \"werks\",\n" +
            "        WS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "        WS.STORAGE_NO AS \"storageNo\",\n" +
            "        WSI.BOX_NOTE AS \"boxNote\",\n" +
            "        WSI.QUANTITY AS \"quantity\",\n" +
            "        (CASE WHEN WSI.UPDATE_TIME IS NULL THEN TO_CHAR(WSI.CREATE_TIME,'yyyy-MM-dd')\n" +
            "            ELSE TO_CHAR(WSI.UPDATE_TIME,'yyyy-MM-dd') END) AS \"createDate\"\n" +
            "    FROM WHS_STORAGE_INVENTORY WSI\n" +
            "        LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "WHERE BOX_NOTE IS NOT NULL AND WS.WERKS = #{werks} AND WSI.QUANTITY > 0\n" +
            "    ORDER BY WS.WHS_LOCATION_CODE, \"createDate\" ASC\n" +
            "    ) RE WHERE 1=1\n" +
            "           AND RE.\"werks\" = #{werks}\n" +
            "       AND TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE,'yyyy-MM-dd'),'yyyy-mm-dd') -  TO_DATE(RE.\"createDate\", 'yyyy-mm-dd')) &gt;= TO_NUMBER(#{days}) \n" +
            " <when test='whsLocationCode!=null and whsLocationCode != \"\"'>AND RE.\"whsLocationCode\" = #{whsLocationCode}</when>\n" +
            " <when test='boxNote!=null and boxNote != \"\"'>AND RE.\"boxNote\" = #{boxNote}</when>\n" +
            "</script>")
    /** 库龄分析(按箱码) */
    List<Map<String, Object>> getBoxInventoryAge(@Param("werks") String werks,
                                                 @Param("whsLocationCode") String whsLocationCode,
                                                 @Param("boxNote") String boxNote,
                                                 @Param("days") Integer days);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.*\n" +
            "FROM (\n" +
            "     SELECT OWO.WERKS AS \"werks\",\n" +
            "            OWO.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "            OWOD.MATERIAL_NO AS \"materialNo\",\n" +
            "            COUNT(OWOD.MATERIAL_NO) AS \"numberOfWhsOut\",\n" +
            "            SUM(OWOD.ACTUAL_QTY_BASIC_UNIT) AS \"quantity\",\n" +
            "            MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "            MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "            MS.BASIC_UNIT AS \"basicUnit\"\n" +
            "     FROM OVERSEA_WHS_OUT OWO\n" +
            "         LEFT JOIN OVERSEA_WHS_OUT_DTL OWOD ON OWO.UUID_WHS_OUT = OWOD.UUID_WHS_OUT\n" +
            "         LEFT JOIN MATERIAL_SAP MS ON OWOD.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "     WHERE OWO.SPMS_STATUS IN ('2', '3', '4') AND OWOD.ACTUAL_QTY_BASIC_UNIT > 0\n" +
            "       <when test='dto.werks!=null and dto.werks != \"\"'>AND OWO.WERKS = #{dto.werks} </when>\n" +
            "       <when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'>AND OWO.WHS_LOCATION_CODE = #{dto.whsLocationCode} </when>\n" +
            "       <when test='dto.materialNo!=null and dto.materialNo != \"\"'>AND OWOD.MATERIAL_NO = #{dto.materialNo} </when>\n" +
            "       <when test='dto.endDate!=null and dto.endDate != \"\"'>AND TO_DATE(OWO.CREATE_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{dto.endDate}, 'yyyy-MM-dd')</when>\n" +
            "       <when test='dto.beginDate!=null and dto.beginDate != \"\"'> AND TO_DATE(OWO.CREATE_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{dto.beginDate}, 'yyyy-MM-dd')</when>" +
            "     GROUP BY OWO.WERKS, OWO.WHS_LOCATION_CODE, OWOD.MATERIAL_NO, MS.MATERIAL_ZH_DESC, MS.MATERIAL_EN_DESC, MS.BASIC_UNIT\n" +
            "     ORDER BY OWO.WERKS, OWO.WHS_LOCATION_CODE, COUNT(OWOD.MATERIAL_NO) DESC, OWOD.MATERIAL_NO\n" +
            "         ) RE" +
            "</script>")
    /** 获取领用出库——出库任务，耗用次数记录 */
    List<Map<String, Object>> getConsumptionRecord(@Param("dto") OverseaConsumptionDTO dto);

    @Select("SELECT COUNT(UUID)\n" +
            "FROM ORDER_WORKSHOP OWS\n" +
            "WHERE OWS.STATUS = '2'\n" +
            "  AND OWS.WERKS IN (SELECT SUW.WERKS FROM SPMS_USER_WORKSHOP SUW WHERE SUW.WORK_CODE = #{userId})\n" +
            "ORDER BY OWS.WERKS ")
    /** 1.需求提报单审批：获取当前人关联工厂下待审批的车间请购单数量；跨工厂合并，全周期； */
    Integer orderWorkshopToDoItemsMap(@Param("userId") String userId);

    @Select("SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.STATUS AS \"status\",\n" +
            "       RE.QUANTITY AS \"quantity\"\n" +
            "FROM (\n" +
            "    SELECT COUNT(STATUS) AS \"QUANTITY\", STATUS\n" +
            "    FROM ORDER_WERKS OW\n" +
            "    WHERE OW.STATUS IN ('1', '4', '6', '8')\n" +
            "      AND OW.WORK_CODE = #{userId}\n" +
            "      AND OW.WERKS IN (\n" +
            "          SELECT SUW.WERKS FROM SPMS_USER_WORKSHOP SUW WHERE SUW.WORK_CODE = #{userId})\n" +
            "    GROUP BY STATUS\n" +
            "    ORDER BY TO_NUMBER(OW.STATUS)\n" +
            "    ) RE ")
    /** 2.工厂请购单：获取本人创建的待提交/待重提SRM/OA审批不通过的工厂请购单数量；跨工厂合并，全周期；
                    各状态单据量；跨工厂合并、近30天； */
    List<Map<String, Object>> orderWerksToDoItemsMap(@Param("userId") String userId);

    @Select("SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.STATUS AS \"status\",\n" +
            "       RE.QUANTITY AS \"quantity\"\n" +
            "FROM (\n" +
            "    SELECT COUNT(SPMS_ID) AS \"QUANTITY\", STATUS\n" +
            "    FROM ORDER_WERKS OW\n" +
            "    WHERE OW.STATUS != '0'\n" +
            "      AND OW.WORK_CODE = #{userId}\n" +
            "      AND OW.WERKS IN (\n" +
            "          SELECT SUW.WERKS FROM SPMS_USER_WORKSHOP SUW WHERE SUW.WORK_CODE = #{userId})\n" +
            "      AND TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE,'yyyy-MM-dd'),'yyyy-mm-dd') - TO_DATE(OW.APPLICATION_DATE,'yyyy-mm-dd')) <= 30\n" +
            "    GROUP BY STATUS\n" +
            "    ORDER BY TO_NUMBER(OW.STATUS)\n" +
            "    ) RE ")
    /** 2.工厂请购单：获取本人创建的待提交/待重提SRM/OA审批不通过的工厂请购单数量；跨工厂合并，全周期；
     各状态单据量；跨工厂合并、近30天； */
    List<Map<String, Object>> orderWerksQuantityEachStatus(@Param("userId") String userId);

    @Select("SELECT COUNT(APPLICATION_UUID) \n" +
            "FROM MATERIAL_APPLICATION MA\n" +
            "WHERE MA.SPMS_STATUS IN ('1', '3')\n" +
            "  AND MA.USER_ID = #{userId}\n" +
            "  AND MA.WERKS IN (\n" +
            "    SELECT SUW.WERKS FROM SPMS_USER_WORKSHOP SUW \n" +
            "    WHERE SUW.WORK_CODE = #{userId})")
    /** 3.非目录物料申请单：待提交——获取本人创建的非目录物料申请待提交OA的数量；跨工厂合并，全周期； */
    Integer materialApplicationToDoItems(@Param("userId") String userId);

    @Select("SELECT COUNT(1)\n" +
            "FROM MATERIAL_APPLICATION MA\n" +
            "    LEFT JOIN MATERIAL_APPLICATION_DTL MAD ON MA.APPLICATION_UUID = MAD.APPLICATION_UUID\n" +
            "WHERE MA.SPMS_STATUS = '4'\n" +
            "  AND MA.USER_ID = #{userId}\n" +
            "  AND MA.WERKS IN (\n" +
            "      SELECT SUW.WERKS FROM SPMS_USER_WORKSHOP SUW WHERE SUW.WORK_CODE = #{userId})\n" +
            "  AND MAD.MATERIAL_NO IS NULL ")
    /** 3.编码未发放——获取本人创建的非目录物料申请，编码为空的明细行数；跨工厂合并，全周期； */
    Integer materialApplicationWithoutMaterialNo(@Param("userId") String userId);

    @Select("SELECT COUNT(1) FROM OVERSEA_REQUISITION ORQ\n" +
            "WHERE ORQ.SPMS_STATUS = '2'\n" +
            "  AND ORQ.WERKS IN (\n" +
            "      SELECT SUW.WERKS FROM SPMS_USER_WORKSHOP SUW\n" +
            "      WHERE SUW.WORK_CODE = #{userId}) ")
    /** 4.车间领用单：待审批——获取当前人关联工厂待审批的领用单；跨工厂合并，全周期； */
    Integer overseaRequisitionToDoItems(@Param("userId") String userId);

    @Select("SELECT COUNT(1)\n" +
            "FROM OVERSEA_REQUISITION ORQ\n" +
            "WHERE ORQ.SPMS_STATUS = '3'\n" +
            "  AND ORQ.CREATE_USERID = #{userId}\n" +
            "  AND ORQ.WERKS IN (\n" +
            "      SELECT SUW.WERKS FROM SPMS_USER_WORKSHOP SUW\n" +
            "      WHERE SUW.WORK_CODE = #{userId}) ")
    /** 4.待领用——获取本人创建的审批通过但未生成出库任务的领用任务；跨工厂合并，全周期； */
    Integer overseaRequisitionWithoutOut(@Param("userId") String userId);

    @Select("SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.STATUS AS \"status\",\n" +
            "       RE.QUANTITY AS \"quantity\"\n" +
            "FROM (\n" +
            "    SELECT COUNT(OWS.SPMS_WS_ID) AS \"QUANTITY\", OWS.STATUS\n" +
            "    FROM ORDER_WORKSHOP OWS\n" +
            "    WHERE OWS.STATUS IN ('1', '2', '3')\n" +
            "      AND OWS.WORK_CODE = #{userId}\n" +
            "      AND OWS.WERKS IN (\n" +
            "          SELECT SUW.WERKS FROM SPMS_USER_WORKSHOP SUW WHERE SUW.WORK_CODE = #{userId})\n" +
            "    GROUP BY OWS.STATUS\n" +
            "    ) RE ")
    /** 5.获取本人创建的需求申请单，待提交 和 审批不通过、已提交三个状态下数量，本人关联工厂内数据；跨工厂合并，全周期； */
    List<Map<String, Object>> orderWorkshopQuantityEachStatus(@Param("userId") String userId);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.*\n" +
            "FROM (\n" +
            "    SELECT DISTINCT OWS.UUID AS \"orderWorkshopUuid\",\n" +
            "                    OWS.WERKS AS \"werks\",\n" +
            "                    OWS.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "                    OWS.SPMS_WS_ID AS \"spmsWsId\",\n" +
            "                    OWS.URGENCY AS \"urgency\",\n" +
            "                    OWS.CREATE_DATE AS \"createDate\",\n" +
            "                    OWS.STATUS AS \"statusOrderWorkshop\",\n" +
            "                    OWS.WORK_CODE AS \"userId\",\n" +
            "                    SU.LAST_NAME AS \"username\",\n" +
            "                    OW.UUID AS \"orderWerksUuid\",\n" +
            "                    OW.SPMS_ID AS \"spmsId\",\n" +
            "                    OW.OA_BIANHAO AS \"oaBianhao\",\n" +
            "                    OW.STATUS AS \"statusOrderWerks\"\n" +
            "    FROM ORDER_WORKSHOP OWS\n" +
            "        LEFT JOIN ORDER_WORKSHOP_DTL OWSD ON OWS.UUID = OWSD.UUID\n" +
            "        LEFT JOIN ORDER_WERKS_DTL OWD ON OWSD.SPMS_WS_ID = OWD.SPMS_WS_ID AND OWSD.ROW_ID = OWD.ROW_ID_WS\n" +
            "        LEFT JOIN ORDER_WERKS OW ON OWD.UUID = OW.UUID\n" +
            "        LEFT JOIN SPMS_USER SU ON OWS.WORK_CODE = SU.WORK_CODE\n" +
            "    WHERE OWS.STATUS != '0'\n" +
            "      AND OWS.WORK_CODE = #{userId}\n" +
            " <when test='dto.werks!=null and dto.werks != \"\"'>AND OWS.WERKS = #{dto.werks} </when>\n" +
            " <when test='dto.workshopCode!=null and dto.workshopCode != \"\"'>AND OWS.WORKSHOP_CODE = #{dto.workshopCode} </when>\n" +
            " <when test='dto.spmsWsId!=null and dto.spmsWsId != \"\"'>AND OWS.SPMS_WS_ID = #{dto.spmsWsId} </when>\n" +
            " <when test='dto.urgency!=null and dto.urgency != \"\"'>AND OWS.URGENCY = #{dto.urgency} </when>\n" +
            " <when test='dto.status!=null and dto.status != \"\"'>AND OWS.STATUS = #{dto.status} </when>\n" +
            " <when test='dto.beginDate!=null and dto.beginDate != \"\"'> AND TO_DATE(OWS.CREATE_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{dto.beginDate}, 'yyyy-MM-dd') </when>" +
            " <when test='dto.endDate!=null and dto.endDate != \"\"'> AND TO_DATE(OWS.CREATE_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{dto.endDate}, 'yyyy-MM-dd') </when>" +
            "  ORDER BY TO_DATE(OWS.CREATE_DATE, 'yyyy-MM-dd') DESC, OWS.SPMS_WS_ID \n" +
            "    ) RE" +
            "</script>")
    /** 海外仓首页，车间请购单整单追溯查询 */
    List<Map<String, Object>> orderWorkshopTrace(IPage iPage,
                                                 @Param("dto") WelcomePageSearchDTO dto,
                                                 @Param("userId") String userId);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.*\n" +
            "FROM (\n" +
            "    SELECT DISTINCT OW.UUID AS \"orderWerksUuid\",\n" +
            "                    OW.SPMS_ID AS \"spmsId\",\n" +
            "                    OW.WERKS AS \"werks\",\n" +
            "                    OW.SUBJECT AS \"subject\",\n" +
            "                    OW.APPLICATION_DATE AS \"applicationDate\",\n" +
            "                    OW.URGENCY AS \"urgency\",\n" +
            "                    OW.STATUS AS \"statusOrderWerks\",\n" +
            "                    OW.WORK_CODE AS \"userId\",\n" +
            "                    OW.OA_BIANHAO AS \"oaBianhao\",\n" +
            "                    SU.LAST_NAME AS \"username\"\n" +
            "    FROM ORDER_WERKS OW\n" +
            "        LEFT JOIN SPMS_USER SU ON OW.WORK_CODE = SU.WORK_CODE\n" +
            "    WHERE OW.STATUS != '0'\n" +
            " <when test='dto.werks!=null and dto.werks != \"\"'>AND OW.WERKS = #{dto.werks} </when>\n" +
            " <when test='dto.spmsId!=null and dto.spmsId != \"\"'>AND OW.SPMS_ID = #{dto.spmsId} </when>\n" +
            " <when test='dto.urgency!=null and dto.urgency != \"\"'>AND OW.URGENCY = #{dto.urgency} </when>\n" +
            " <when test='dto.status!=null and dto.status != \"\"'>AND OW.STATUS = #{dto.status} </when>\n" +
            " <when test='dto.beginDate!=null and dto.beginDate != \"\"'> AND TO_DATE(OW.APPLICATION_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{dto.beginDate}, 'yyyy-MM-dd') </when>" +
            " <when test='dto.endDate!=null and dto.endDate != \"\"'> AND TO_DATE(OW.APPLICATION_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{dto.endDate}, 'yyyy-MM-dd') </when>" +
            "    ORDER BY TO_DATE(OW.APPLICATION_DATE, 'yyyy-MM-dd') DESC, OW.SPMS_ID\n" +
            "    ) RE" +
            "</script>")
    /** 海外仓首页，工厂请购单整单追溯查询 */
    List<Map<String, Object>> orderWerksTrace(IPage iPage, @Param("dto")WelcomePageWerksOrderDTO dto);

    // 以下注释部分为追溯方式：先按工厂请购单 uuid 获取工厂请购单明细行；再根据每一行明细行的 uuid + 行号，查询关联箱码及所有箱码当前位置；
    //@Select("SELECT OW.UUID AS \"orderWerksUuid\",\n" +
    //        "       OW.SPMS_ID AS \"spmsId\",\n" +
    //        "       OWD.ROW_ID_WERKS AS \"rowIdWerks\",\n" +
    //        "       OWS.UUID AS \"orderWorkshopUuid\",\n" +
    //        "       OWS.SPMS_WS_ID AS \"spmsWsId\",\n" +
    //        "       OWSD.ROW_ID AS \"rowIdWorkshop\",\n" +
    //        "       OWSD.MATERIAL_NO AS \"materialNo\",\n" +
    //        "       OWSD.REQUEST_QTY AS \"requestQty\",\n" +
    //        "       OWSD.APPROVAL_QTY AS \"approvalQty\",\n" +
    //        "       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
    //        "       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
    //        "       MS.BASIC_UNIT AS \"basicUnit\",\n" +
    //        "       MS.UNIT_SALES AS \"unitSales\"\n" +
    //        "FROM ORDER_WERKS OW\n" +
    //        "    LEFT JOIN ORDER_WERKS_DTL OWD ON OW.UUID = OWD.UUID\n" +
    //        "    LEFT JOIN ORDER_WORKSHOP_DTL OWSD ON OWD.SPMS_WS_ID = OWSD.SPMS_WS_ID AND OWD.ROW_ID_WS = OWSD.ROW_ID\n" +
    //        "    LEFT JOIN ORDER_WORKSHOP OWS ON OWSD.UUID = OWS.UUID\n" +
    //        "    LEFT JOIN MATERIAL_SAP MS ON OWSD.MATERIAL_NO = MS.MATERIAL_NO\n" +
    //        "WHERE OWSD.APPROVAL_QTY > 0 AND OW.UUID = #{orderWerksUuid}\n" +
    //        "ORDER BY OW.SPMS_ID DESC, TO_NUMBER(OWD.ROW_ID_WERKS) ")
    ///** 按工厂请购单uuid查询所有明细行 */
    //List<Map<String, Object>> orderWerksDtlList(@Param("orderWerksUuid") String orderWerksUuid);
    //
    //@Select("SELECT W.UUID AS \"orderWerksUuid\",\n" +
    //        "       W.SPMS_ID AS \"spmsId\",\n" +
    //        "       WD.ROW_ID_WERKS AS \"rowIdWerks\",\n" +
    //        "       SBND.BOX_NOTE AS \"boxNote\",\n" +
    //        "       SBND.ITEM AS \"boxNoteItem\",\n" +
    //        "       SBND.MATERIAL_NO AS \"materialNo\",\n" +
    //        "       SBND.DOMESTIC_QTY_RECEIVED AS \"domesticQtyReceived\",\n" +
    //        "       BOX_LOG.WERKS AS \"werks\",\n" +
    //        "       BOX_LOG.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
    //        "       BOX_LOG.OPERATION_TYPE AS \"operationType\",\n" +
    //        "       (CASE WHEN SBND.BOX_NOTE IS NULL THEN '箱码待生成'\n" +
    //        "           WHEN BOX_LOG.OPERATION_TYPE IN ('2', '7', '12', '18', '19') THEN '自管仓'\n" +
    //        "           WHEN BOX_LOG.OPERATION_TYPE = '8' THEN '盘亏'\n" +
    //        "           WHEN BOX_LOG.OPERATION_TYPE IN ('3', '16', '20', '22') THEN '自管仓已发出'\n" +
    //        "           WHEN BOX_LOG.OPERATION_TYPE = '21' THEN '海外仓已到货'\n" +
    //        "           ELSE '自管仓待到货' END) AS \"boxStatus\",\n" +
    //        "       BOX_LOG.CREATE_TIME AS \"createTime\" \n" +
    //        "FROM ORDER_WERKS_DTL WD\n" +
    //        "    LEFT JOIN ORDER_WERKS W ON WD.UUID = W.UUID\n" +
    //        "    LEFT JOIN SAP_BOX_NOTE_DTL SBND ON SBND.DEMAND_NO = W.SPMS_ID AND SBND.DEMAND_NO_ITEM = WD.ROW_ID_WERKS\n" +
    //        "    LEFT JOIN (\n" +
    //        "        SELECT RE.ID, L.WERKS, L.WHS_LOCATION_CODE, L.BOX_NOTE, L.DOCUMENT_TYPE,\n" +
    //        "               L.OPERATION_TYPE, L.COMMENTS, L.REMARK, L.CREATE_TIME\n" +
    //        "        FROM (\n" +
    //        "            SELECT MAX(LOG_AUTO_ID) AS \"ID\"\n" +
    //        "            FROM WHS_OPERATE_LOG WOL\n" +
    //        "            WHERE DOCUMENT_TYPE IN ('2', '3') AND BOX_NOTE IS NOT NULL GROUP BY BOX_NOTE\n" +
    //        "            ) RE LEFT JOIN WHS_OPERATE_LOG L ON RE.ID = L.LOG_AUTO_ID\n" +
    //        "        ORDER BY TO_NUMBER(L.OPERATION_TYPE) DESC\n" +
    //        "        ) BOX_LOG ON SBND.BOX_NOTE = BOX_LOG.BOX_NOTE\n" +
    //        "    LEFT JOIN SAP_BOX_NOTE BN ON SBND.BOX_NOTE = BN.BOX_NOTE\n" +
    //        "WHERE W.UUID = #{orderWerksUuid} AND WD.ROW_ID_WERKS = #{rowIdWerks}\n" +
    //        "ORDER BY W.SPMS_ID DESC, TO_NUMBER(WD.ROW_ID_WERKS), SBND.BOX_NOTE NULLS FIRST \n")
    ///** 按工厂请购单uuid 和 行号查询明细行关联所有箱码所在位置 */
    //List<Map<String, Object>> orderWerksBoxTraceList(@Param("orderWerksUuid") String orderWerksUuid, @Param("rowIdWerks") String rowIdWerks);

    @Select("SELECT W.UUID AS \"orderWerksUuid\",\n" +
            "       W.SPMS_ID AS \"spmsId\",\n" +
            "       WD.ROW_ID_WERKS AS \"rowIdWerks\",\n" +
            "       WSD.UUID AS \"orderWorkshopUuid\",\n" +
            "       WSD.ROW_ID AS \"rowIdWorkshop\",\n" +
            "       WSD.SPMS_WS_ID AS \"spmsWsId\",\n" +
            "       WSD.REQUEST_QTY AS \"requestQty\",\n" +
            "       WSD.APPROVAL_QTY AS \"approvalQty\",\n" +
            "       WSD.MATERIAL_NO AS \"materialNo\",\n" +
            "       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "       MS.UNIT_SALES AS \"unitSales\",\n" +
            "       SBND.BOX_NOTE AS \"boxNote\",\n" +
            "       SBND.ITEM AS \"boxNoteItem\",\n" +
            "       SBND.DOMESTIC_QTY_RECEIVED AS \"domesticQtyReceived\",\n" +
            "       BOX_LOG.WERKS AS \"werks\",\n" +
            "       BOX_LOG.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       BOX_LOG.OPERATION_TYPE AS \"operationType\",\n" +
            "       (CASE WHEN SBND.BOX_NOTE IS NULL THEN '1'\n" +
            "           WHEN BOX_LOG.OPERATION_TYPE IN ('2', '7', '12', '18', '19') THEN '3'\n" +
            "           WHEN BOX_LOG.OPERATION_TYPE = '8' THEN '4'\n" +
            "           WHEN BOX_LOG.OPERATION_TYPE IN ('3', '16', '20', '22') THEN '5'\n" +
            "           WHEN BOX_LOG.OPERATION_TYPE = '21' THEN '6'\n" +
            "           ELSE '2' END) AS \"boxStatus\"," +
            "       (CASE WHEN SBND.BOX_NOTE IS NULL THEN '箱码待生成'\n" +
            "           WHEN BOX_LOG.OPERATION_TYPE IN ('2', '7', '12', '18', '19') THEN '自管仓'\n" +
            "           WHEN BOX_LOG.OPERATION_TYPE = '8' THEN '盘亏'\n" +
            "           WHEN BOX_LOG.OPERATION_TYPE IN ('3', '16', '20', '22') THEN '自管仓已发出'\n" +
            "           WHEN BOX_LOG.OPERATION_TYPE = '21' THEN '海外仓已到货'\n" +
            "           ELSE '自管仓待到货' END) AS \"boxStatusDesc\",\n" +
            "       BOX_LOG.CREATE_TIME AS \"createTime\"\n" +
            "FROM ORDER_WERKS_DTL WD\n" +
            "    LEFT JOIN ORDER_WERKS W ON WD.UUID = W.UUID\n" +
            "    LEFT JOIN ORDER_WORKSHOP_DTL WSD ON WD.SPMS_WS_ID = WSD.SPMS_WS_ID AND WSD.ROW_ID = WD.ROW_ID_WS\n" +
            "    LEFT JOIN SAP_BOX_NOTE_DTL SBND ON SBND.DEMAND_NO = W.SPMS_ID AND SBND.DEMAND_NO_ITEM = WD.ROW_ID_WERKS\n" +
            "    LEFT JOIN (\n" +
            "        SELECT RE.ID, L.WERKS, L.WHS_LOCATION_CODE, L.BOX_NOTE, L.DOCUMENT_TYPE,\n" +
            "               L.OPERATION_TYPE, L.COMMENTS, L.REMARK, L.CREATE_TIME\n" +
            "        FROM (\n" +
            "            SELECT MAX(LOG_AUTO_ID) AS \"ID\"\n" +
            "            FROM WHS_OPERATE_LOG WOL\n" +
            "            WHERE DOCUMENT_TYPE IN ('2', '3') AND BOX_NOTE IS NOT NULL GROUP BY BOX_NOTE\n" +
            "            ) RE LEFT JOIN WHS_OPERATE_LOG L ON RE.ID = L.LOG_AUTO_ID\n" +
            "        ORDER BY TO_NUMBER(L.OPERATION_TYPE) DESC\n" +
            "        ) BOX_LOG ON SBND.BOX_NOTE = BOX_LOG.BOX_NOTE\n" +
            "    LEFT JOIN MATERIAL_SAP MS ON MS.MATERIAL_NO = WSD.MATERIAL_NO\n" +
            "WHERE W.UUID = #{orderWerksUuid}\n" +
            "ORDER BY W.SPMS_ID DESC, TO_NUMBER(WD.ROW_ID_WERKS), SBND.BOX_NOTE NULLS FIRST \n")
    /** 按工厂请购单uuid 和 行号查询明细行关联所有箱码所在位置 */
    List<Map<String, Object>> orderWerksBoxTraceListByUuid(@Param("orderWerksUuid") String orderWerksUuid);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", RESULT.* FROM (\n" +
            "    SELECT OW.WERKS AS \"werks\",\n" +
            "           W.WERKS_NAME_ZH AS \"werksNameZh\",\n" +
            "           OWS.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "           WS.WORKSHOP_NAME_ZH AS \"workshopNameZh\",\n" +
            "           OWS.WORK_CODE AS \"orderWorkshopUserid\",\n" +
            "           OWS.LAST_NAME AS \"orderWorkshopUsername\",\n" +
            "           OW.WORK_CODE AS \"orderWerksUserid\",\n" +
            "           OW.LAST_NAME AS \"orderWerksUsername\",\n" +
            "           OW.SPMS_ID AS \"orderWerksId\",\n" +
            "           OWD.ROW_ID_WERKS AS \"orderWerksRowId\",\n" +
            "           OW.OA_PROCESS_ID AS \"oaProcessId\",\n" +
            "           OW.APPLICATION_DATE AS \"orderWerksApplicationDate\",\n" +
            "           OW.OA_APPROVAL_DATE AS \"oaApprovalDate\",\n" +
            "           OW.URGENCY AS \"urgency\",\n" +
            "           OW.FIELD_FOR_OA AS \"orderWerksFieldForOa\",\n" +
            "           OW.SHIPPING_TYPE AS \"orderWerksShippingType\",\n" +
            "           OW.SHIPPING_FANGSHI AS \"orderWerksShippingFangshi\",\n" +
            "           OW.JUSTIFICATION AS \"orderWerksJustification\",\n" +
            "           OWS.SUBJECT AS \"orderWorkshopSubject\",\n" +
            "           OWS.SPMS_WS_ID AS \"orderWorkshopId\",\n" +
            "           OWSD.ROW_ID AS \"orderWorkshopRowId\",\n" +
            "           OWS.APPLICATION_DATE AS \"orderWorkshopApplicationDate\",\n" +
            "           OWSD.MATERIAL_NO AS \"materialNo\",\n" +
            "           SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "           SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "           OWSD.REQUEST_QTY AS \"requestQty\",\n" +
            "           OWSD.APPROVAL_QTY AS \"approvalQty\",\n" +
            "           SAP.BASIC_UNIT AS \"basicUnit\",\n" +
            "           NVL(SI.SAFETY_QTY_BASIC_UNIT, 0) AS \"safetyQtyBasicUnit\",\n" +
            "           NVL(WI.WERKS_INVENTORY, 0) AS \"werksInventory\",\n" +
            "           NVL(WAY.WAY_QUANTITY, 0) AS \"wayQuantity\",\n" +
            "           SBND2.BOX_NOTE AS \"boxNote\",\n" +
            "           SBND2.ITEM AS \"boxNoteItem\",\n" +
            "           SBND2.DOMESTIC_QTY_RECEIVED AS \"domesticQtyReceived\",\n" +
            "           BOX_LOG.OPERATION_TYPE AS \"operationType\",\n" +
            "           (CASE WHEN SBND2.BOX_NOTE IS NULL THEN '1'\n" +
            "               WHEN BOX_LOG.OPERATION_TYPE IN ('2', '7', '12', '18', '19') THEN '3'\n" +
            "               WHEN BOX_LOG.OPERATION_TYPE = '8' THEN '4'\n" +
            "               WHEN BOX_LOG.OPERATION_TYPE IN ('3', '16', '20', '22') THEN '5'\n" +
            "               WHEN BOX_LOG.OPERATION_TYPE = '21' THEN '6'\n" +
            "               ELSE '2' END) AS \"boxStatus\",\n" +
            "           (CASE WHEN SBND2.BOX_NOTE IS NULL THEN '箱码待生成'\n" +
            "               WHEN BOX_LOG.OPERATION_TYPE IN ('2', '7', '12', '18', '19') THEN '自管仓'\n" +
            "               WHEN BOX_LOG.OPERATION_TYPE = '8' THEN '盘亏'\n" +
            "               WHEN BOX_LOG.OPERATION_TYPE IN ('3', '16', '20', '22') THEN '自管仓已发出'\n" +
            "               WHEN BOX_LOG.OPERATION_TYPE = '21' THEN '海外仓已到货'\n" +
            "               ELSE '自管仓待到货' END) AS \"boxStatusDesc\",\n" +
            "           SBND2.DELIVERY_NOTE_SAP AS \"deliveryNote\",\n" +
            "           (CASE WHEN SDN.DOCUMENT_DATE IS NOT NULL\n" +
            "                      THEN TO_CHAR(TO_DATE(SDN.DOCUMENT_DATE, 'yyyyMMdd'), 'yyyy-MM-dd')\n" +
            "               END) AS \"domesticArrivalDate\",\n" +
            "           SPLB.PACKAGE_LIST AS \"packageList\",\n" +
            "           SPLB.WHS_OUT_UUID AS \"whsOutUuid\",\n" +
            "           (CASE WHEN CHN_OUT.CREATE_TIME IS NOT NULL\n" +
            "                      THEN TO_CHAR(CHN_OUT.CREATE_TIME, 'yyyy-MM-dd')\n" +
            "               END) AS \"domesticDeliveryDate\",\n" +
            "           (CASE WHEN OVERSEA_BOX.CREATE_TIME IS NOT NULL\n" +
            "                      THEN TO_CHAR(OVERSEA_BOX.CREATE_TIME, 'yyyy-MM-dd')\n" +
            "               END) AS \"overseaArrivalDate\",\n" +
            "           (CASE WHEN OVERSEA_BOX.CREATE_TIME IS NOT NULL\n" +
            "                      THEN TO_DATE(TO_CHAR(OVERSEA_BOX.CREATE_TIME, 'yyyy-MM-dd'), 'yyyy-MM-dd') -\n" +
            "                           TO_DATE(OWS.APPLICATION_DATE, 'yyyy-MM-dd')\n" +
            "               END) AS \"daysSpent\"\n" +
            "    FROM ORDER_WERKS OW\n" +
            "        LEFT JOIN ORDER_WERKS_DTL OWD ON OW.UUID = OWD.UUID\n" +
            "        LEFT JOIN ORDER_WORKSHOP_DTL OWSD ON OWD.SPMS_WS_ID = OWSD.SPMS_WS_ID AND OWD.ROW_ID_WS = OWSD.ROW_ID\n" +
            "        LEFT JOIN ORDER_WORKSHOP OWS ON OWSD.UUID = OWS.UUID\n" +
            "        LEFT JOIN WERKS W ON OW.WERKS = W.WERKS\n" +
            "        LEFT JOIN WORKSHOP WS ON OW.WERKS = WS.WERKS AND OWS.WORKSHOP_CODE = WS.WORKSHOP_CODE\n" +
            "        LEFT JOIN MATERIAL_SAP SAP ON OWSD.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "        LEFT JOIN SAFETY_INVENTORY SI ON OW.WERKS = SI.WERKS AND OWSD.MATERIAL_NO = SI.MATERIAL_NO\n" +
            "        LEFT JOIN (\n" +
            "            SELECT WS.WERKS, WSI.MATERIAL_NO, SUM(WSI.QUANTITY) AS \"WERKS_INVENTORY\"\n" +
            "            FROM WHS_STORAGE_INVENTORY WSI\n" +
            "                LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "            GROUP BY WS.WERKS, WSI.MATERIAL_NO\n" +
            "            ) WI ON OW.WERKS = WI.WERKS AND OWSD.MATERIAL_NO = WI.MATERIAL_NO\n" +
            "        LEFT JOIN (\n" +
            "            SELECT T.WERKS, T.MATERIAL_NO, SUM(T.DOMESTIC_QTY_RECEIVED) AS \"WAY_QUANTITY\"\n" +
            "            FROM (\n" +
            "                SELECT SUBSTR(SBND.DEMAND_NO, 0, 4) AS \"WERKS\", SBND.MATERIAL_NO, SBND.DOMESTIC_QTY_RECEIVED\n" +
            "                FROM (\n" +
            "                    SELECT MAX(LOG_AUTO_ID) AS \"ID\", L.BOX_NOTE\n" +
            "                    FROM WHS_OPERATE_LOG L\n" +
            "                    WHERE L.BOX_NOTE IS NOT NULL AND L.WERKS = 'CN01'\n" +
            "                      AND L.OPERATION_TYPE IN ('3', '16', '22') AND L.COMMENTS != '自管仓备件入库撤销下架'\n" +
            "                    GROUP BY L.BOX_NOTE\n" +
            "                    ) R LEFT JOIN SAP_BOX_NOTE_DTL SBND ON R.BOX_NOTE = SBND.BOX_NOTE\n" +
            "                ) T GROUP BY T.WERKS, T.MATERIAL_NO\n" +
            "            ) WAY ON OW.WERKS = WAY.WERKS AND OWSD.MATERIAL_NO = WAY.MATERIAL_NO\n" +
            "        LEFT JOIN SAP_BOX_NOTE_DTL SBND2 ON OW.SPMS_ID = SBND2.DEMAND_NO AND OWD.ROW_ID_WERKS = SBND2.DEMAND_NO_ITEM\n" +
            "        LEFT JOIN (\n" +
            "            SELECT BL.ID, L.WERKS, L.WHS_LOCATION_CODE, L.BOX_NOTE, L.DOCUMENT_TYPE, L.OPERATION_TYPE, L.COMMENTS, L.REMARK, L.CREATE_TIME\n" +
            "            FROM (\n" +
            "                SELECT MAX(LOG_AUTO_ID) AS \"ID\"\n" +
            "                FROM WHS_OPERATE_LOG WOL\n" +
            "                WHERE WOL.DOCUMENT_TYPE IN ('2', '3') AND WOL.BOX_NOTE IS NOT NULL GROUP BY BOX_NOTE\n" +
            "                )BL LEFT JOIN WHS_OPERATE_LOG L ON BL.ID = L.LOG_AUTO_ID ORDER BY TO_NUMBER(L.OPERATION_TYPE) DESC\n" +
            "            ) BOX_LOG ON BOX_LOG.BOX_NOTE = SBND2.BOX_NOTE\n" +
            "        LEFT JOIN (\n" +
            "            SELECT DISTINCT SPB.BOX_NOTE, SPB.PACKAGE_LIST, SPB.WHS_OUT_UUID FROM SAP_PACKAGE_BOX SPB WHERE ACTUAL_OPERATE_QTY > 0\n" +
            "            ) SPLB ON SBND2.BOX_NOTE = SPLB.BOX_NOTE\n" +
            "        LEFT JOIN SAP_DELIVERY_NOTE SDN ON SBND2.DELIVERY_NOTE_SAP = SDN.DELIVERY_NOTE_SAP\n" +
            "        LEFT JOIN WHS_OUT WO ON SPLB.WHS_OUT_UUID = WO.UUID\n" +
            "        LEFT JOIN (\n" +
            "            SELECT T.ID, T.BOX_NOTE, L.CREATE_TIME FROM (\n" +
            "                SELECT MAX(WOL.LOG_AUTO_ID) AS \"ID\", WOL.BOX_NOTE\n" +
            "                FROM WHS_OPERATE_LOG WOL\n" +
            "                WHERE WOL.BOX_NOTE IS NOT NULL AND WOL.WERKS = 'CN01'\n" +
            "                  AND WOL.OPERATION_TYPE IN ('3', '16', '22') AND WOL.COMMENTS != '自管仓备件入库撤销下架'\n" +
            "                GROUP BY WOL.BOX_NOTE\n" +
            "                ) T LEFT JOIN WHS_OPERATE_LOG L ON T.ID = L.LOG_AUTO_ID\n" +
            "            ) CHN_OUT ON SBND2.BOX_NOTE = CHN_OUT.BOX_NOTE\n" +
            "        LEFT JOIN (\n" +
            "            SELECT MAX(OL.LOG_AUTO_ID) AS \"ID\", OL.BOX_NOTE, OL.CREATE_TIME\n" +
            "            FROM WHS_OPERATE_LOG OL\n" +
            "            WHERE OL.BOX_NOTE IS NOT NULL AND OL.OPERATION_TYPE = '21'\n" +
            "            GROUP BY OL.BOX_NOTE, OL.CREATE_TIME\n" +
            "            ) OVERSEA_BOX ON SBND2.BOX_NOTE = OVERSEA_BOX.BOX_NOTE\n" +
            "    WHERE OW.WERKS = #{dto.werks}\n" +
            "    <when test='dto.spmsId!=null and dto.spmsId != \"\"'>AND OW.SPMS_ID = #{dto.spmsId} </when>\n" +
            "    <when test='dto.beginDate!=null and dto.beginDate != \"\"'>AND TO_DATE(OW.APPLICATION_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{dto.beginDate}, 'yyyy-MM-dd')</when>\n" +
            "    <when test='dto.endDate!=null and dto.endDate != \"\"'>AND TO_DATE(OW.APPLICATION_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{dto.endDate}, 'yyyy-MM-dd')</when>\n" +
            "    ORDER BY OW.SPMS_ID, OWD.ROW_ID_WERKS, SBND2.BOX_NOTE NULLS LAST, SBND2.ITEM\n" +
            "    ) RESULT " +
            "</script>")
    List<Map<String, Object>> getOrderKeyDates(@Param("dto") OverseaReport dto);

    /** getOrderKeyDates 对应完整SQL语句如下：
     SELECT ROWNUM AS "rowId", RESULT.* FROM (
     SELECT OW.WERKS AS "werks",
     W.WERKS_NAME_ZH AS "werksNameZh",
     OWS.WORKSHOP_CODE AS "workshopCode",
     WS.WORKSHOP_NAME_ZH AS "workshopNameZh",
     OWS.WORK_CODE AS "orderWorkshopUserid",
     OWS.LAST_NAME AS "orderWorkshopUsername",
     OW.WORK_CODE AS "orderWerksUserid",
     OW.LAST_NAME AS "orderWerksUsername",
     OW.SPMS_ID AS "orderWerksId",
     OWD.ROW_ID_WERKS AS "orderWerksRowId",
     OW.OA_PROCESS_ID AS "oaProcessId",
     OW.APPLICATION_DATE AS "orderWerksApplicationDate",
     OW.OA_APPROVAL_DATE AS "oaApprovalDate",
     OW.URGENCY AS "urgency",
     OW.FIELD_FOR_OA AS "orderWerksFieldForOa",
     OW.SHIPPING_TYPE AS "orderWerksShippingType",
     OW.SHIPPING_FANGSHI AS "orderWerksShippingFangshi",
     OW.JUSTIFICATION AS "orderWerksJustification",
     OWS.SUBJECT AS "orderWorkshopSubject",
     OWS.SPMS_WS_ID AS "orderWorkshopId",
     OWSD.ROW_ID AS "orderWorkshopRowId",
     OWS.APPLICATION_DATE AS "orderWorkshopApplicationDate",
     OWSD.MATERIAL_NO AS "materialNo",
     SAP.MATERIAL_ZH_DESC AS "materialZhDesc",
     SAP.MATERIAL_EN_DESC AS "materialEnDesc",
     OWSD.REQUEST_QTY AS "requestQty",
     OWSD.APPROVAL_QTY AS "approvalQty",
     SAP.BASIC_UNIT AS "basicUnit",
     NVL(SI.SAFETY_QTY_BASIC_UNIT, 0) AS "safetyQtyBasicUnit",
     NVL(WI.WERKS_INVENTORY, 0) AS "werksInventory",
     NVL(WAY.WAY_QUANTITY, 0) AS "wayQuantity",
     SBND2.BOX_NOTE AS "boxNote",
     SBND2.ITEM AS "boxNoteItem",
     SBND2.DOMESTIC_QTY_RECEIVED AS "domesticQtyReceived",
     BOX_LOG.OPERATION_TYPE AS "operationType",
     (CASE WHEN SBND2.BOX_NOTE IS NULL THEN '1'
     WHEN BOX_LOG.OPERATION_TYPE IN ('2', '7', '12', '18', '19') THEN '3'
     WHEN BOX_LOG.OPERATION_TYPE = '8' THEN '4'
     WHEN BOX_LOG.OPERATION_TYPE IN ('3', '16', '20', '22') THEN '5'
     WHEN BOX_LOG.OPERATION_TYPE = '21' THEN '6'
     ELSE '2' END) AS "boxStatus",
     (CASE WHEN SBND2.BOX_NOTE IS NULL THEN '箱码待生成'
     WHEN BOX_LOG.OPERATION_TYPE IN ('2', '7', '12', '18', '19') THEN '自管仓'
     WHEN BOX_LOG.OPERATION_TYPE = '8' THEN '盘亏'
     WHEN BOX_LOG.OPERATION_TYPE IN ('3', '16', '20', '22') THEN '自管仓已发出'
     WHEN BOX_LOG.OPERATION_TYPE = '21' THEN '海外仓已到货'
     ELSE '自管仓待到货' END) AS "boxStatusDesc",
     SBND2.DELIVERY_NOTE_SAP AS "deliveryNote",
     (CASE WHEN SDN.DOCUMENT_DATE IS NOT NULL
     THEN TO_CHAR(TO_DATE(SDN.DOCUMENT_DATE, 'yyyyMMdd'), 'yyyy-MM-dd')
     END) AS "domesticArrivalDate",
     SPLB.PACKAGE_LIST AS "packageList",
     SPLB.WHS_OUT_UUID AS "whsOutUuid",
     (CASE WHEN CHN_OUT.CREATE_TIME IS NOT NULL
     THEN TO_CHAR(CHN_OUT.CREATE_TIME, 'yyyy-MM-dd')
     END) AS "domesticDeliveryDate",
     (CASE WHEN OVERSEA_BOX.CREATE_TIME IS NOT NULL
     THEN TO_CHAR(OVERSEA_BOX.CREATE_TIME, 'yyyy-MM-dd')
     END) AS "overseaArrivalDate",
     (CASE WHEN OVERSEA_BOX.CREATE_TIME IS NOT NULL
     THEN TO_DATE(TO_CHAR(OVERSEA_BOX.CREATE_TIME, 'yyyy-MM-dd'), 'yyyy-MM-dd') -
     TO_DATE(OWS.APPLICATION_DATE, 'yyyy-MM-dd')
     END) AS "daysSpent"
     FROM ORDER_WERKS OW
     LEFT JOIN ORDER_WERKS_DTL OWD ON OW.UUID = OWD.UUID
     LEFT JOIN ORDER_WORKSHOP_DTL OWSD ON OWD.SPMS_WS_ID = OWSD.SPMS_WS_ID AND OWD.ROW_ID_WS = OWSD.ROW_ID
     LEFT JOIN ORDER_WORKSHOP OWS ON OWSD.UUID = OWS.UUID
     LEFT JOIN WERKS W ON OW.WERKS = W.WERKS
     LEFT JOIN WORKSHOP WS ON OW.WERKS = WS.WERKS AND OWS.WORKSHOP_CODE = WS.WORKSHOP_CODE
     LEFT JOIN MATERIAL_SAP SAP ON OWSD.MATERIAL_NO = SAP.MATERIAL_NO
     LEFT JOIN SAFETY_INVENTORY SI ON OW.WERKS = SI.WERKS AND OWSD.MATERIAL_NO = SI.MATERIAL_NO
     LEFT JOIN (
     SELECT WS.WERKS, WSI.MATERIAL_NO, SUM(WSI.QUANTITY) AS "WERKS_INVENTORY"
     FROM WHS_STORAGE_INVENTORY WSI
     LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID
     GROUP BY WS.WERKS, WSI.MATERIAL_NO
     ) WI ON OW.WERKS = WI.WERKS AND OWSD.MATERIAL_NO = WI.MATERIAL_NO
     LEFT JOIN (
     SELECT T.WERKS, T.MATERIAL_NO, SUM(T.DOMESTIC_QTY_RECEIVED) AS "WAY_QUANTITY"
     FROM (
     SELECT SUBSTR(SBND.DEMAND_NO, 0, 4) AS "WERKS", SBND.MATERIAL_NO, SBND.DOMESTIC_QTY_RECEIVED
     FROM (
     SELECT MAX(LOG_AUTO_ID) AS "ID", L.BOX_NOTE
     FROM WHS_OPERATE_LOG L
     WHERE L.BOX_NOTE IS NOT NULL AND L.WERKS = 'CN01'
     AND L.OPERATION_TYPE IN ('3', '16', '22') AND L.COMMENTS != '自管仓备件入库撤销下架'
     GROUP BY L.BOX_NOTE
     ) R LEFT JOIN SAP_BOX_NOTE_DTL SBND ON R.BOX_NOTE = SBND.BOX_NOTE
     ) T GROUP BY T.WERKS, T.MATERIAL_NO
     ) WAY ON OW.WERKS = WAY.WERKS AND OWSD.MATERIAL_NO = WAY.MATERIAL_NO
     LEFT JOIN SAP_BOX_NOTE_DTL SBND2 ON OW.SPMS_ID = SBND2.DEMAND_NO AND OWD.ROW_ID_WERKS = SBND2.DEMAND_NO_ITEM
     LEFT JOIN (
     SELECT BL.ID, L.WERKS, L.WHS_LOCATION_CODE, L.BOX_NOTE, L.DOCUMENT_TYPE, L.OPERATION_TYPE, L.COMMENTS, L.REMARK, L.CREATE_TIME
     FROM (
     SELECT MAX(LOG_AUTO_ID) AS "ID"
     FROM WHS_OPERATE_LOG WOL
     WHERE WOL.DOCUMENT_TYPE IN ('2', '3') AND WOL.BOX_NOTE IS NOT NULL GROUP BY BOX_NOTE
     )BL LEFT JOIN WHS_OPERATE_LOG L ON BL.ID = L.LOG_AUTO_ID ORDER BY TO_NUMBER(L.OPERATION_TYPE) DESC
     ) BOX_LOG ON BOX_LOG.BOX_NOTE = SBND2.BOX_NOTE
     LEFT JOIN (
     SELECT DISTINCT SPB.BOX_NOTE, SPB.PACKAGE_LIST, SPB.WHS_OUT_UUID FROM SAP_PACKAGE_BOX SPB WHERE ACTUAL_OPERATE_QTY > 0
     ) SPLB ON SBND2.BOX_NOTE = SPLB.BOX_NOTE
     LEFT JOIN SAP_DELIVERY_NOTE SDN ON SBND2.DELIVERY_NOTE_SAP = SDN.DELIVERY_NOTE_SAP
     LEFT JOIN WHS_OUT WO ON SPLB.WHS_OUT_UUID = WO.UUID
     LEFT JOIN (
     SELECT T.ID, T.BOX_NOTE, L.CREATE_TIME FROM (
     SELECT MAX(WOL.LOG_AUTO_ID) AS "ID", WOL.BOX_NOTE
     FROM WHS_OPERATE_LOG WOL
     WHERE WOL.BOX_NOTE IS NOT NULL AND WOL.WERKS = 'CN01'
     AND WOL.OPERATION_TYPE IN ('3', '16', '22') AND WOL.COMMENTS != '自管仓备件入库撤销下架'
     GROUP BY WOL.BOX_NOTE
     ) T LEFT JOIN WHS_OPERATE_LOG L ON T.ID = L.LOG_AUTO_ID
     ) CHN_OUT ON SBND2.BOX_NOTE = CHN_OUT.BOX_NOTE
     LEFT JOIN (
     SELECT MAX(OL.LOG_AUTO_ID) AS "ID", OL.BOX_NOTE, OL.CREATE_TIME
     FROM WHS_OPERATE_LOG OL
     WHERE OL.BOX_NOTE IS NOT NULL AND OL.OPERATION_TYPE = '21'
     GROUP BY OL.BOX_NOTE, OL.CREATE_TIME
     ) OVERSEA_BOX ON SBND2.BOX_NOTE = OVERSEA_BOX.BOX_NOTE
     WHERE OW.WERKS = 'TF01'
     ORDER BY OW.SPMS_ID, OWD.ROW_ID_WERKS, SBND2.BOX_NOTE NULLS LAST, SBND2.ITEM
     ) RESULT
     */
}


